<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>SQL Functions : DataMapper ORM - User Guide</title>

<link rel="shortcut icon" type="image/png" href="../images/favicon.png" />
<link rel="stylesheet" type="text/css" media="all" href="../css/userguide.css" />
<link rel="alternate" type="application/rss+xml" title="Datamapper ORM Updates Feed" href="/rss.xml" />

<meta http-equiv="expires" content="-1" />
<meta http-equiv= 'pragma' content="no-cache" />
<meta name="robots" content="all" />

</head>

<body>

<!-- START NAVIGATION -->
<div id="nav"><div id="nav_inner"></div></div>
<div id="nav2"><a name="top">&nbsp;</a><a id="nav_toggle" href="#"><img src="../images/nav_toggle_darker.jpg" width="154" height="43" border="0" title="Toggle Table of Contents" alt="Toggle Table of Contents" /></a></div>
<div id="masthead">
<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
<tr>
<td><h1>DataMapper ORM</h1></td>
<td id="breadcrumb_right"><a href="toc.html">Table of Contents Page</a></td>
</tr>
</table>
</div>
<!-- END NAVIGATION -->

<!-- START BREADCRUMB -->
<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
<tr>
<td id="breadcrumb">
<a href="/">Datamapper ORM Home</a> &nbsp;&#8250;&nbsp;
<a href="../index.html">User Guide Home</a> &nbsp;&#8250;&nbsp;
SQL Functions
</td>
</tr>

</table>
<!-- END BREADCRUMB -->

<br clear="all" />


<!-- START CONTENT -->
<div id="content">

<h1>SQL Functions</h1>

<p>If you want to include SQL functions &mdash; including user-defined SQL functions &mdash; it is easier that ever with Datamapper ORM.  There are several ways to access custom SQL functions.</p>

<h2 id="func">$object->func($function_name, $arg1, $arg2, ...)</h2>
<p>The first is by directly creating one using the <var><u>func</u></var> method.  This method builds a SQL function, and processes a variety of arguments.</p>
<ul>
	<li><b>Operators</b>: Mathematical and String operators, such as <kbd>+</kbd>, <kbd>&</kbd>, or <kbd>||</kbd> are inserted directly.</li>
	<li><b>Pre-Escaped Strings</b>: If a string starts and ends with a single quote mark (<dfn>'</dfn>), or is the special string <dfn>'*'</dfn>, it is added directly.</li>
	<li><b>Raw Strings</b>: If a string starts and ends with square brackets (<dfn>[ ]</dfn>), the string (without brackets) is inserted directly without escaping.</li>
	<li><b>Non-Strings</b>: Non strings are included in the SQL directly, such as numbers and boolean values.</li>
	<li><b>Column Names</b>: Column names, or fields on a model, are strings that start with an at-symbol (<dfn>@</dfn>).  These are replaced with properly protected names.</li>
	<li><b>Related Column Names</b>: Related column names start with an <dfn>@</dfn>, but contain forward slashes to reference one or more relationships.</li>
	<li><b>Formulas</b>: Passing in a set of arguments in an array is concatenated as a formula.
		In a formula, common operators are not escaped.
		Formulas can also recusively reference functions, as seen below.</li>
	<li><b>Simple Strings</b>: Normal strings are escaped to be used in the function as SQL strings.</li>
</ul>

<div class="important">
	<p>Please note that if user-provided content starts and stops with single-quote marks, or starts with an @ sign, the input <strong>may be inserted into the query without escaping</strong></p>
	<p>If you are planning on working with user-provided input, it may be wise to pre-escape this content with <var>$object</var><kbd>-&gt;</kbd><var>db</var><kbd>-&gt;</kbd><var>escape_str</var><kbd>()</kbd>.</p>
</div>


<h3>Random Examples</h3>

<pre>
<var>$u </var><kbd>= new </kbd><var>User</var><kbd>();

</kbd><samp>// UPPER('hello')
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>func</u></var><kbd>(</kbd><dfn>'UPPER'</dfn><kbd>, </kbd><dfn>'hello'</dfn><kbd>);

</kbd><samp>// round(365 * `users`.`age`)
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>func</u></var><kbd>(</kbd><dfn>'round'</dfn><kbd>, array(</kbd><var>365</var><kbd>, </kbd><dfn>'*'</dfn><kbd>, </kbd><dfn>'@age'</dfn><kbd>));

</kbd><samp>// round(sqrt(`users`.`id`))
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>func</u></var><kbd>(</kbd><dfn>'round'</dfn><kbd>, array(</kbd><dfn>'sqrt' </dfn><kbd>=&gt; </kbd><dfn>'@id'</dfn><kbd>));

</kbd><samp>// COALESCE(`users`.`name`, '')
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>func</u></var><kbd>(</kbd><dfn>'COALESCE'</dfn><kbd>, </kbd><dfn>'@name'</dfn><kbd>, </kbd><dfn>''</dfn><kbd>);

</kbd><samp>//Adds `group` table, and returns UPPER(`groups`.`name`)
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>func</u></var><kbd>(</kbd><dfn>'UPPER'</dfn><kbd>, </kbd><dfn>'@group/name'</dfn><kbd>);

</kbd><samp>// Trick to get a formula with no function
// (365 * `users`.`age`)
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>func</u></var><kbd>(</kbd><dfn>''</dfn><kbd>, array(</kbd><var>365</var><kbd>, </kbd><dfn>'*'</dfn><kbd>, </kbd><dfn>'@age'</dfn><kbd>));</kbd>
</pre>

<p>Where the method is really powerful is that you can combine column names from either the direct table <i>or</i> from related models with functions and properties.</p>

<h2 id="select_func">$object->select_func($function_name, [$arg1, [...]], $alias)</h2>

<p>In this format, the result of the function is added to the select statement.  The last argument is always used as the alias, and is required.</p>
<div class="important" id="select_func.AR.Bug">
	<strong><em>CodeIgniter ActiveRecord Bug</em></strong>
	<p>
		CodeIgniter has an overly aggressive method for protecting identifiers, and it <strong>cannot</strong> be disabled.
		This may break any attempt to include functions in the SELECT statement.
	</p>
	<p>However, with a simple adjustment to the <var>_protect_identifiers</var> method of the <var>DB_driver</var> class, you can get it working again.</p>
	<p><a href="#Protect.Identifiers.Fix">See the bottom of this page for the code modification.</a></p>
</div>
<h3>Examples</h3>
<pre>
<var>$u </var><kbd>= new </kbd><var>User</var><kbd>();

</kbd><samp>// SELECT `users`.*, UPPER(`users`.`name`) as uppercase_name
// FROM `users`
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>select_func</u></var><kbd>(</kbd><dfn>'UPPER'</dfn><kbd>, </kbd><dfn>'@name'</dfn><kbd>, </kbd><dfn>'uppercase_name'</dfn><kbd>)-&gt;</kbd><var><u>get</u></var><kbd>();

</kbd><samp>// SELECT `users`.*, (`groups`.`name` = 'Administrators') as is_admin
// FROM `users`
// LEFT OUTER JOIN `groups` as groups ON `groups`.`id` = `users`.`group_id`
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>select_func</u></var><kbd>(</kbd><dfn>''</dfn><kbd>, array(</kbd><dfn>'@group/name'</dfn><kbd>, </kbd><dfn>'='</dfn><kbd>, </kbd><dfn>'Administrators'</dfn><kbd>), </kbd><dfn>'is_admin'</dfn><kbd>)-&gt;</kbd><var><u>get</u></var><kbd>();</kbd>
</pre>


<h2 id="query_func">$object->{query}_func($function_name, [$arg1, [$arg2, [...]], $value)</h2>

<p>
	This format makes it easier to use formulas in queries.  Not all methods have been tested at this time.
	The last parameter is <strong>required</strong>, and is passed to the <a href="getadvanced.html#Supported.Query.Clauses">supported query clause</a>.
</p>

<h3>Example</h3>
<pre>
<var>$u </var><kbd>= new </kbd><var>User</var><kbd>();

</kbd><samp>// SELECT `users`.*
// FROM `users`
// ORDER BY LOWER(`users`.`lastname` &amp; ', ' &amp; `users`.`firstname`) ASC
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>order_by_func</u></var><kbd>(</kbd><dfn>'LOWER'</dfn><kbd>, array(</kbd><dfn>'@lastname'</dfn><kbd>, </kbd><dfn>'&amp;'</dfn><kbd>, </kbd><dfn>', '</dfn><kbd>, </kbd><dfn>'&amp;'</dfn><kbd>, </kbd><dfn>'@firstname'</dfn><kbd>), </kbd><dfn>'ASC'</dfn><kbd>);
</kbd><var>$u</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();</kbd>
</pre>

<h2 id="query_field_func">$object->{query}_field_func($field, $function_name, [$arg1, [$arg2, [...]])</h2>

<p>
	This format is used for comparing database fields to the result of a function.
	The first parameter is the field name, and is passed to the <a href="getadvanced.html#Supported.Query.Clauses">supported query clause</a>.
</p>

<h3>Example</h3>
<pre>
<var>$u </var><kbd>= new </kbd><var>User</var><kbd>();

</kbd><samp>// SELECT `users`.*
// FROM `users`
// WHERE `users`.`birthdate` &lt;= getLimitBirthdate(21)
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>where_field_func</u></var><kbd>(</kbd><dfn>'birthdate <='</dfn><kbd>, </kbd><dfn>'getLimitBirthdate'</dfn><kbd>, </kbd><var>21</var><kbd>);
</kbd><var>$u</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();</kbd>
</pre>


<p>&nbsp;</p><p>&nbsp;</p>

<h1 id="Protect.Identifiers.Fix">Fixing the Protect Identifiers Method</h1>
<p>
	Modifying the <var>CI_DB_driver</var><kbd>::</kbd><var>_protect_identifiers</var> method as directed will help fix most problems with AR changing data.
	You can also "escape" any possibly protected data by wrapping it in parentheses.
</p>
<p class="note"><strong><em>Please Note:</em></strong> If you upgrade your CodeIgniter installation, you'll have to make this change again!</p>
<p>In the file <var>system/database/DB_driver.php</var>, simply move the highlighted section, and remove <kbd>.</kbd><var>$alias</var> from the return line.</p>
<h4>system/database/DB_driver.php - v1.7.2 (Original)</h4>
<pre><div class="lineNums">1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254</div><var>        </var><samp>// Convert tabs or multiple spaces into single spaces
        </samp><var>$item </var><kbd>= </kbd><var><b>preg_replace</b></var><kbd>(</kbd><dfn>'/[\t ]+/'</dfn><kbd>, </kbd><dfn>' '</dfn><kbd>, </kbd><var>$item</var><kbd>);

        </kbd><samp>// If the item has an alias declaration we remove it and set it aside.
        // Basically we remove everything to the right of the first space
        </samp><var>$alias </var><kbd>= </kbd><dfn>''</dfn><kbd>;
        if (</kbd><var><b>strpos</b></var><kbd>(</kbd><var>$item</var><kbd>, </kbd><dfn>' '</dfn><kbd>) !== </kbd><var><dfn>FALSE</dfn></var><kbd>)
        {
            </kbd><var>$alias </var><kbd>= </kbd><var><b>strstr</b></var><kbd>(</kbd><var>$item</var><kbd>, </kbd><dfn>" "</dfn><kbd>);
            </kbd><var>$item </var><kbd>= </kbd><var><b>substr</b></var><kbd>(</kbd><var>$item</var><kbd>, </kbd><var>0</var><kbd>, - </kbd><var><b>strlen</b></var><kbd>(</kbd><var>$alias</var><kbd>));
        }</kbd>

<span class="new">        <samp>// This is basically a bug fix for queries that use MAX, MIN, etc.
        // If a parenthesis is found we know that we do not need to
        // escape the data or add a prefix.  There's probably a more graceful
        // way to deal with this, but I'm not thinking of it -- Rick
        </samp><kbd>if (</kbd><var><b>strpos</b></var><kbd>(</kbd><var>$item</var><kbd>, </kbd><dfn>'('</dfn><kbd>) !== </kbd><var><dfn>FALSE</dfn></var><kbd>)
        {
            return </kbd><var>$item</var><strike><kbd>.</kbd><var>$alias</var></strike><kbd>;
        }</kbd></span>
</pre>

	<h4>system/database/DB_driver.php - v1.7.2 (Modified)</h4>
<pre><div class="lineNums">1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254</div><span class="new"><var>        </var><samp>// This is basically a bug fix for queries that use MAX, MIN, etc.
        // If a parenthesis is found we know that we do not need to
        // escape the data or add a prefix.  There's probably a more graceful
        // way to deal with this, but I'm not thinking of it -- Rick
        </samp><kbd>if (</kbd><var><b>strpos</b></var><kbd>(</kbd><var>$item</var><kbd>, </kbd><dfn>'('</dfn><kbd>) !== </kbd><var><dfn>FALSE</dfn></var><kbd>)
        {
            return </kbd><var>$item</var><kbd>; </kbd><samp>// Note this is different!
        </samp><kbd>}</kbd></span>

        <samp>// Convert tabs or multiple spaces into single spaces
        </samp><var>$item </var><kbd>= </kbd><var><b>preg_replace</b></var><kbd>(</kbd><dfn>'/[\t ]+/'</dfn><kbd>, </kbd><dfn>' '</dfn><kbd>, </kbd><var>$item</var><kbd>);

        </kbd><samp>// If the item has an alias declaration we remove it and set it aside.
        // Basically we remove everything to the right of the first space
        </samp><var>$alias </var><kbd>= </kbd><dfn>''</dfn><kbd>;
        if (</kbd><var><b>strpos</b></var><kbd>(</kbd><var>$item</var><kbd>, </kbd><dfn>' '</dfn><kbd>) !== </kbd><var><dfn>FALSE</dfn></var><kbd>)
        {
            </kbd><var>$alias </var><kbd>= </kbd><var><b>strstr</b></var><kbd>(</kbd><var>$item</var><kbd>, </kbd><dfn>" "</dfn><kbd>);
            </kbd><var>$item </var><kbd>= </kbd><var><b>substr</b></var><kbd>(</kbd><var>$item</var><kbd>, </kbd><var>0</var><kbd>, - </kbd><var><b>strlen</b></var><kbd>(</kbd><var>$alias</var><kbd>));
        }</kbd>
</pre>

</div>
<!-- END CONTENT -->


<div id="footer">
<p>
<span id="footer_previous">Previous Topic:&nbsp;&nbsp;<a href=""></a>
&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;</span>
<a href="#top">Top of Page</a>&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
<a href="../index.html">User Guide Home</a>
<span id="footer_next">&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
Next Topic:&nbsp;&nbsp;<a href=""></a></span>
</p>
<div id="copyrights">
<p><a href="/">Datamapper ORM</a> &nbsp;&middot;&nbsp; Copyright &copy; 2010-2011 &nbsp;&middot;&nbsp; Harro "WanWizard" Verton</p>
<p><a href="license.html">Other License Information</a></p>
</div>
</div>

<script type="text/javascript" src="../js/mootools.js"></script>
<script type="text/javascript" src="../js/menu.js"></script>
<script type="text/javascript">
<!--
	window.addEvent('domready', function() {

		// Create Menu
		var menu = new Menu({
			basepath: '../',
			pagespath: ''
		});

	});
//-->
</script>
</body>
</html>
